﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using BaseHelper.Data;
using System.Collections;
using System.Diagnostics;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
using BaseHelper.Base;

namespace DBank
{
    public partial class FrmMain : Form
    {
        ConfigHelper _configHelper = new ConfigHelper();
        public FrmMain()
        {
            InitializeComponent();
        }

        private void FrmMain_Load(object sender, EventArgs e)
        {
            M.DBInfo = new DBInfo();

            _configHelper.Load(SysHelper.AppPath + "\\config.dat");

            _configHelper.Get(cboxConfigDatabaseSQLType, "1");
            _configHelper.Get(txtConfigDatabaseSQLServer, "127.0.0.1");
            _configHelper.Get(txtConfigDatabaseSQLName, "rtdatabase");
            _configHelper.Get(txtConfigDatabaseSQLUser, "root");
            _configHelper.Get(txtConfigDatabaseSQLPass, "");
            _configHelper.Get(txtConfigDatabaseSQLPort, "");
            _configHelper.Get(txtConfigDatabaseSQLCharSet, "utf8");

            M.DBInfo.SourceType = (DBType)cboxConfigDatabaseSQLType.SelectedIndex;
            M.DBInfo.IP = txtConfigDatabaseSQLServer.Text;
            M.DBInfo.DBName = txtConfigDatabaseSQLName.Text;
            M.DBInfo.User = txtConfigDatabaseSQLUser.Text;
            M.DBInfo.Pass = txtConfigDatabaseSQLPass.Text;
            M.DBInfo.Port = txtConfigDatabaseSQLPort.Text;
            M.DBInfo.CharSet = txtConfigDatabaseSQLCharSet.Text;

            lvDBTableName.Columns.Add("序号", 60);
            lvDBTableName.Columns.Add("表名称", 180);
            lvDBTableName.Columns.Add("数据量", 100);
            lvDBTableName.Columns.Add("状态", 400);
            lvDBTableName.Columns.Add("路径", 0);
        }

        private void txtConfigDatabaseSave_Click(object sender, EventArgs e)
        {
            M.DBInfo.SourceType = (DBType)cboxConfigDatabaseSQLType.SelectedIndex;
            M.DBInfo.IP = txtConfigDatabaseSQLServer.Text;
            M.DBInfo.DBName = txtConfigDatabaseSQLName.Text;
            M.DBInfo.User = txtConfigDatabaseSQLUser.Text;
            M.DBInfo.Pass = txtConfigDatabaseSQLPass.Text;
            M.DBInfo.Port = txtConfigDatabaseSQLPort.Text;
            M.DBInfo.CharSet = txtConfigDatabaseSQLCharSet.Text;
            
            _configHelper.Save();

            MessageBox.Show("保存完成！");
        }

        private void btnSqlTest_Click(object sender, EventArgs e)
        {
            DBHelper db = new DBHelper();
            DBInfo dbInfo = new DBInfo();
            dbInfo.SourceType = (DBType)cboxConfigDatabaseSQLType.SelectedIndex;
            dbInfo.IP = txtConfigDatabaseSQLServer.Text;
            dbInfo.DBName = txtConfigDatabaseSQLName.Text;
            dbInfo.User = txtConfigDatabaseSQLUser.Text;
            dbInfo.Pass = txtConfigDatabaseSQLPass.Text;
            dbInfo.Port = txtConfigDatabaseSQLPort.Text;
            dbInfo.CharSet = txtConfigDatabaseSQLCharSet.Text;
            if (db.Open(dbInfo))
            {
                MessageBox.Show("连接主机成功！", "成功");
                db.Close();
            }
            else
            {
                MessageBox.Show(db.Error, "连接失败");
            }
        }

        private void btnDataTable_Click(object sender, EventArgs e)
        {
            M m = new M();
            string[] tables = m.GetTables();

            lvDBTableName.Items.Clear();
            if (tables.Length>0)
            {
                foreach (string tableName in tables)
                {
                    string rowCount =m.Table(tableName).Count().ToString();

                    ListViewItem lvi = new ListViewItem();
                    lvi.Text = (lvDBTableName.Items.Count + 1).ToString();
                    lvi.SubItems.Add(tableName);
                    lvi.SubItems.Add(rowCount);
                    lvi.SubItems.Add("");
                    lvi.SubItems.Add("");
                    lvDBTableName.Items.Add(lvi);

                }
            }
        }

        private void btnDataOutput_Click(object sender, EventArgs e)
        {
            M m = new M();
            FolderBrowserDialog fd = new FolderBrowserDialog();
            if (fd.ShowDialog() != DialogResult.OK)
                return;

            string folderPath = fd.SelectedPath;

            foreach (ListViewItem lvi in lvDBTableName.CheckedItems)
            {
                string tableName = lvi.SubItems[1].Text;
                string datFile = folderPath + "\\" + tableName + ".dat";

                if (File.Exists(datFile))
                {
                    try
                    {
                        File.Delete(datFile);
                    }
                    catch { }
                }
                lvi.SubItems[3].Text = "检索数据...";
                Application.DoEvents();

                Hashtable[] hts = m.Select(tableName, "", "*", "");

                if (hts == null)
                {
                    lvi.SubItems[3].Text = "无数据！";
                    Application.DoEvents();
                }
                else
                {
                    try
                    {
                        lvi.SubItems[3].Text = "写入文件...";
                        Application.DoEvents();

                        FileStream fs = new FileStream(datFile, FileMode.Create);
                        BinaryFormatter bf = new BinaryFormatter();
                        bf.Serialize(fs, hts);
                        fs.Close();
                        lvi.SubItems[3].Text = "导出完成!";
                        Application.DoEvents();

                    }
                    catch (Exception ex)
                    {
                        lvi.SubItems[3].Text = "写入文件出错：" + ex;
                        Application.DoEvents();
                    }
                }

            }
        }

        private void btnDataFile_Click(object sender, EventArgs e)
        {
            OpenFileDialog of = new OpenFileDialog();
            of.Multiselect = true;
            if (of.ShowDialog() != DialogResult.OK)
                return;

            lvDBTableName.Items.Clear();
            if (of.FileNames != null)
            {
                foreach (string file in of.FileNames)
                {
                    FileInfo f = new FileInfo(file);

                    ListViewItem lvi = new ListViewItem();
                    double fileSize = (float)f.Length / 1024.0f;

                    lvi.Text = (lvDBTableName.Items.Count + 1).ToString();
                    lvi.SubItems.Add(f.Name.Replace(f.Extension, ""));
                    lvi.SubItems.Add(fileSize.ToString("0") + "kb");
                    lvi.SubItems.Add("");
                    lvi.SubItems.Add(file);
                    lvi.Checked = true;
                    lvDBTableName.Items.Add(lvi);

                }
            }
        }

        private void btnDataImport_Click(object sender, EventArgs e)
        {
    
            M.DBInfo.KeepLive = true;

            M m = new M();
            if (!m.Open(M.DBInfo))
            {
                MessageBox.Show("数据库打开失败！");
                return;
            }

            foreach (ListViewItem lvi in lvDBTableName.CheckedItems)
            {
                string tableName = lvi.SubItems[1].Text;
                string datFile = lvi.SubItems[4].Text;

                lvi.SubItems[3].Text = "读取数据...";
                Application.DoEvents();

                Hashtable[] hts;
                try
                {
                    FileStream fs = new FileStream(datFile, FileMode.Open);
                    BinaryFormatter bf = new BinaryFormatter();
                    hts = (Hashtable[])bf.Deserialize(fs);
                    fs.Close();
                }
                catch (Exception ex)
                {
                    lvi.SubItems[3].Text = "读取文件失败：" + ex.Message;
                    Application.DoEvents();
                    continue;
                }
                if (hts == null)
                {
                    lvi.SubItems[3].Text = "跳过,空数据！";
                    Application.DoEvents();
                    continue;
                }

                lvi.SubItems[3].Text = "读取成功...";
                Application.DoEvents();

                string[] colsHts = m.GetFields(tableName);
                if (colsHts.Length==0)
                {
                    lvi.SubItems[3].Text = "跳过,读取字段为空!";
                    Application.DoEvents();
                    continue;
                }

                lvi.SubItems[3].Text = "清空表数据,开始插入数据..." + hts.Length.ToString();
                Application.DoEvents();

                m.Truncate(tableName);

                if (m.SourceType == DBType.MSSQL)
                    m.NonQuery("set identity_insert " + tableName + " ON");

                Hashtable cols = new Hashtable();
                if (cols != null)
                {
                    foreach (string h in colsHts)
                        cols.Add(h, null);
                }

                int succ = 0;

                List<string> keyRemove = new List<string>();
                foreach (string key in hts[0].Keys)
                {
                    if (!cols.ContainsKey(key))
                        keyRemove.Add(key);
                }

                Stopwatch sw = new Stopwatch();
                sw.Start();
                foreach (Hashtable h in hts)
                {
                    if (keyRemove.Count > 0)
                    {
                        foreach (string key in keyRemove)
                        {
                            if (!cols.ContainsKey(key))
                                h.Remove(key);
                        }
                    }
                    //all++;
                    //if (m.Insert(tableName, h, true) > 0)
                    //    succ++;

                    //lvi.SubItems[3].Text = succ.ToString() + "/" + all.ToString() +"/"+ hts.Length.ToString();
                    //Application.DoEvents();

                }

                if (hts.Length > 10000)
                {
                    succ = m.Insert(tableName, hts);

                    lvi.SubItems[3].Text = succ.ToString() + "/" + hts.Length.ToString();
                    Application.DoEvents();
                }
                else
                {
                    foreach (Hashtable h in hts)
                    {
                        if (m.Insert(tableName, h, true) > 0)
                            succ++;

                        lvi.SubItems[3].Text = succ.ToString() + "/" + hts.Length.ToString();
                        Application.DoEvents();
                    }
                }
                sw.Stop();
                if (m.SourceType == DBType.MSSQL)
                    m.NonQuery("set identity_insert " + tableName + " OFF");

                bool isSuccess = succ == hts.Length;
                lvi.BackColor = isSuccess ? Color.Transparent : Color.Yellow;
                lvi.SubItems[3].Text = "导入完成，成功导入：" + succ.ToString() + "，总数量：" + hts.Length.ToString() + "，耗时：" + sw.Elapsed.TotalSeconds.ToString("0") + "s";
                Application.DoEvents();


            }
        }

        private void btnDBClear_Click(object sender, EventArgs e)
        {
            lvDBTableName.Items.Clear();
        }

        private void chkDBCheckAll_CheckedChanged(object sender, EventArgs e)
        {
            foreach (ListViewItem lvi in lvDBTableName.Items)
                lvi.Checked = chkDBCheckAll.Checked;

        }
    }
}
